﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Sy.DbHelper;
using System.Data.Common;
using System.Web;
using System.IO;
using System.Net;
using SY.SFTPHelper;
using System.Collections;
using System.Net.Mail;
using Microsoft.Exchange.WebServices;
using Microsoft.Exchange.WebServices.Data;
using System.Timers;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        System.Timers.Timer timer = new System.Timers.Timer();
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            

            try
            {
                DbHelper db = new DbHelper();
                DbCommand cmd;
                string sql;
                string FDate;
                FDate = dateTimePicker1.Text.ToString();

                sql = "SELECT replace(FNumber,char(9),'') as FNumber ,replace(FBillNo,char(9),'') as FBillNo ,replace(FHeadSelfI0450,char(9),'') as FHeadSelfI0450,replace(FHeadSelfI0454,char(9),'') as FHeadSelfI0454," +
                       " replace(FNumber,char(9),'') as SO,replace(FAlias,char(9),'') as FAlias,replace(FDate,char(9),'') as FDate, replace(FHelpCode,char(9),'') as FHelpCode," +
                       " replace(FName1,char(9),'') as FName1,replace(FName2,char(9),'') as FName2, replace(FQty,char(9),'') as FQty,replace(FtaxPrice,char(9),'') as FtaxPrice," +
                       " replace(FAmountincludetax,char(9),'') as FAmountincludetax,replace(FTaxRate,char(9),'') as FTaxRate,replace(币种,char(9),'') as 币种,replace(FModel,char(9),'') as FModel," +
                       " replace(FNote,char(9),'') as FNote,replace(FHeadSelfI0453,char(9),'') as FHeadSelfI0453,replace(审核人,char(9),'') as 审核人,replace(FTranType,char(9),'') as FTranType," +
                       " replace(新产品线201304,char(9),'') as 新产品线201304,replace(新产品线201403,char(9),'') as 新产品线201403,replace(金税发票号,char(9),'') as 金税发票号,replace(FAddress,char(9),'') as FAddress," +
                       " replace(FPhone,char(9),'') as FPhone,replace(FAccount,char(9),'') as FAccount,replace(Fnumber2,char(9),'') as Fnumber2,replace(FAmount,char(9),'') as FAmount," +
                       " replace(FTaxAmount,char(9),'') as FTaxAmount,replace(FTaxNum,char(9),'') as FTaxNum,replace(业务员,char(9),'') as 业务员,replace(FName,char(9),'') as FName,replace(FPrice,char(9),'') as FPrice,账套 FROM ( " +
                       " SELECT v_icsale_full.FNumber as FNumber, v_icsale_full.FBillNo as FBillNo, v_icsale_full.FHeadSelfI0450 as FHeadSelfI0450, v_icsale_full.FHeadSelfI0454 as FHeadSelfI0454," +
                       " v_icsale_full.SO as SO,t_ICItem.FAlias as FAlias,convert(varchar(10),v_icsale_full.FDate,126) as FDate, v_icsale_full.FHelpCode as FHelpCode," +
                       " v_icsale_full.FName1 as FName1,v_icsale_full.FName2 as FName2, v_icsale_full.FQty as FQty,v_icsale_full.FtaxPrice as FtaxPrice," +
                       " v_icsale_full.FAmountincludetax as FAmountincludetax,v_icsale_full.FTaxRate as FTaxRate, v_icsale_full.币种 as 币种,v_icsale_full.FModel as FModel," +
                       " v_icsale_full.FNote as FNote, v_icsale_full.FHeadSelfI0453 as FHeadSelfI0453,  v_icsale_full.审核人 as 审核人, v_icsale_full.FTranType as FTranType," +
                       " v_icsale_full.新产品线201304 as 新产品线201304, v_icsale_full.新产品线201403 as 新产品线201403, v_icsale_full.金税发票号 as 金税发票号,v_icsale_full.FAddress as FAddress," +
                       " v_icsale_full.FPhone as FPhone, v_icsale_full.FAccount as FAccount, v_icsale_full.Fnumber2 as Fnumber2,  v_icsale_full.FAmount as FAmount," +
                       " v_icsale_full.FTaxAmount as FTaxAmount,v_icsale_full.FTaxNum as FTaxNum,v_icsale_full.业务员 as 业务员, v_icsale_full.FName as FName,  v_icsale_full.FPrice AS FPrice,'BID' as 账套,FCheckDate " +
                       " FROM {oj AIS20101225174316.dbo.v_icsale_full v_icsale_full LEFT OUTER JOIN AIS20101225174316.dbo.t_ICItem t_ICItem ON v_icsale_full.Fnumber2 = t_ICItem.FNumber} " +
                       " union all " +
                       " SELECT v_icsale_full.FNumber as FNumber, v_icsale_full.FBillNo as FBillNo, v_icsale_full.FHeadSelfI0450 as FHeadSelfI0450, v_icsale_full.FHeadSelfI0454 as FHeadSelfI0454," +
                       " v_icsale_full.SO as SO,t_ICItem.FAlias as FAlias,convert(varchar(10),v_icsale_full.FDate,126) as FDate, v_icsale_full.FHelpCode as FHelpCode," +
                       " v_icsale_full.FName1 as FName1,v_icsale_full.FName2 as FName2, v_icsale_full.FQty as FQty,v_icsale_full.FtaxPrice as FtaxPrice," +
                       " v_icsale_full.FAmountincludetax as FAmountincludetax,v_icsale_full.FTaxRate as FTaxRate, v_icsale_full.币种 as 币种,v_icsale_full.FModel as FModel," +
                       " v_icsale_full.FNote as FNote, v_icsale_full.FHeadSelfI0453 as FHeadSelfI0453,  v_icsale_full.审核人 as 审核人, v_icsale_full.FTranType as FTranType," +
                       " v_icsale_full.新产品线201304 as 新产品线201304, v_icsale_full.新产品线201403 as 新产品线201403, v_icsale_full.金税发票号 as 金税发票号,v_icsale_full.FAddress as FAddress," +
                       " v_icsale_full.FPhone as FPhone, v_icsale_full.FAccount as FAccount, v_icsale_full.Fnumber2 as Fnumber2,  v_icsale_full.FAmount as FAmount," +
                       " v_icsale_full.FTaxAmount as FTaxAmount,v_icsale_full.FTaxNum as FTaxNum,v_icsale_full.业务员 as 业务员, v_icsale_full.FName as FName,  v_icsale_full.FPrice AS  FPrice,'MIDDBJ' as 账套	,FCheckDate " +
                       " FROM {oj AIS20070306163042.dbo.v_icsale_full v_icsale_full LEFT OUTER JOIN AIS20070306163042.dbo.t_ICItem t_ICItem ON v_icsale_full.Fnumber2 = t_ICItem.FNumber} )A " +
                       " WHERE FCheckDate>='" + FDate +"' And FCheckDate<='" + FDate +"'";
                cmd = db.GetSqlStringCommand(sql);
                DataTable dt = db.ExecuteDataTable(cmd);
                //string FDate = DateTime.Now.ToString("yyyy-MM-dd");
                string sFilePath = "C:\\K3";

                if (!Directory.Exists(sFilePath))//验证路径是否存在
                {
                    Directory.CreateDirectory(sFilePath);
                    //不存在则创建
                }

                ExportExcelFile(dt, "C:\\K3\\BIBJ Sales report " + FDate + ".txt");
                MessageBox.Show("文件生成成功请在C:\\K3\\下查看");
            }
            catch
            {
                SFTPHelper SFT = new SFTPHelper("47.97.60.80", "22", "ftpdatauser", "I#3FcJS1Lb%#");
                SFT.SendMailUse("数据库连接出现问题,请管理员查看", "BIBJ Sales report");
                
            }
            //db.close();
        }


        public static void ExportExcelFile(DataTable dt, string fullPath)
        {

            if (File.Exists(fullPath))
                File.Delete(fullPath);

           

            var fi = new FileInfo(fullPath); 
            if (!fi.Directory.Exists) { fi.Directory.Create(); }
            var fs = new FileStream(fullPath, FileMode.Append, FileAccess.Write);            
            //StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);           
            var sw = new StreamWriter(fs, Encoding.UTF8);
            var data = "";            //写出列名称         
            int s = 0;
            if (s == 0)
            {
                ++s;
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    data += dt.Columns[i].ColumnName;
                    if (i < dt.Columns.Count - 1)
                    {
                        data += "\t";
                    }
                }
                sw.WriteLine(data);
            }
            //写出各行数据       
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                data = "";
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var str = dt.Rows[i][j].ToString();
                    str = str.Replace("\"", "\"\""); //替换英文冒号 英文冒号需要换成两个冒号  
                    if (str.Contains(',') || str.Contains('"') || str.Contains('\r') || str.Contains('\n')) //含逗号 冒号 换行符的需要放到引号中         
                    {
                        str = string.Format("\"{0}\"", str);
                    }
                    data += str;
                    if (j < dt.Columns.Count - 1)
                    {
                        data += "\t";
                    }
                }
                sw.WriteLine(data);
            }
            sw.Close();
            fs.Close();
        }

        /// 上传ftp服务
        /// </summary>
        /// <param name="path">文件地址</param>
        /// <returns></returns>
        public string Upload(string path)
        {
            var client = new WebClient();
            client.Credentials = new NetworkCredential("administrator", "123");//用户名和密码
            
            client.BaseAddress = "ftp://192.168.109.128";//ftp地址
            string FDate = DateTime.Now.ToString("yyyy-MM-dd");
            string ftpPath = client.BaseAddress + "/opt/ftpdata/BIBJ/" + "BIBJ Sales report " + FDate + ".txt";//上传fptp路径
            string returnPath = "";
            try
            {
                client.UploadFile(ftpPath, path);
                returnPath = ftpPath;
            }
            catch (Exception ex)
            {
                ///错误信息处理
            }
            return returnPath;
        }

        private void button2_Click(object sender, EventArgs e) //FTP
        {
            string FDate = DateTime.Now.ToString("yyyy-MM-dd");
            Upload("C:\\K3\\BIBJ Sales report " + FDate + ".txt");
        }


        private void button3_Click(object sender, EventArgs e) //SFTP
        {
            int i;
            string  Names;
            SFTPHelper SFT = new SFTPHelper("47.97.60.80", "22", "ftpdatauser", "I#3FcJS1Lb%#"); //"192.168.2.100", "22", "Administrator", "891025"   //"47.97.60.80", "22", "ftpdatauser", "I#3FcJS1Lb%#")
            string FDate;
            FDate = dateTimePicker1.Text.ToString();

            //SFT.GetFileList("K3/", ".txt");
             SFT.Put("C:\\K3\\BIBJ Sales report " + FDate + ".txt", "BIBJ/BIBJ Sales report " + FDate + ".txt");    //opt/ftpdata/BIBJ

            Names = "BIBJ Sales report " + FDate + ".txt";

            ArrayList al = new ArrayList();
            ///opt/ftpdata//BIBJ
            al = SFT.GetFileList("BIBJ/", ".txt");
            //SFT.Get()
            for(i=0;i<al.Count ;i++)
            {
              if (Names==  al[i].ToString())
              {
                  MessageBox.Show("SFT移动文件成功");
                  //SFT.WriteLog("SFT移动文件成功");
                 // SendMailUse("SFTP移动文件成功", "BIBJ Sales report");
              }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            timer.Enabled = true;
            
            timer.Interval = 1000;//执行间隔时间,单位为毫秒  
            timer.Start();
            // 指定每天时间1执行
            timer.Elapsed += new System.Timers.ElapsedEventHandler(Timer1_Elapsed);
            timer.AutoReset = true; //设置一直执行(true)
            timer1.Enabled = true; //是否执行System.Timers.Timer.Elapsed事件
            button4.Enabled = false;

        }
         private void Timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            int intHour = e.SignalTime.Hour;
            int intMinute = e.SignalTime.Minute;
            int intSecond = e.SignalTime.Second;
            bool FBoolSetTXT;

             if (intMinute.ToString() == "1" && intHour.ToString() == "5" && intSecond.ToString() == "1")
             {
                FBoolSetTXT= SetTXT();
                 if (FBoolSetTXT ==true )
                 {
                     GetTXT();
                 }
             }
        }

         private  bool SetTXT()
         {
             try
             {
                 DbHelper db = new DbHelper();
                 DbCommand cmd;
                 string sql;
                 string FDate = DateTime.Now.AddDays(-1).ToShortDateString(); 
                 //FDate = dateTimePicker1.Text.ToString();

                 sql = "SELECT  replace(replace(replace(FNumber,char(9),''),char(10),''),char(13),'') as FNumber ,replace(replace(replace(FBillNo,char(9),''),char(10),''),char(13),'') as FBillNo ," +
                        "replace(FHeadSelfI0450,char(9),'') as FHeadSelfI0450,replace(FHeadSelfI0454,char(9),'') as FHeadSelfI0454," +
                        " replace(SO,char(9),'') as SO,replace(FAlias,char(9),'') as FAlias,replace(FDate,char(9),'') as FDate, replace(FHelpCode,char(9),'') as FHelpCode," +
                        " replace(FName1,char(9),'') as FName1,replace(FName2,char(9),'') as FName2, replace(FQty,char(9),'') as FQty,replace(FtaxPrice,char(9),'') as FtaxPrice," +
                        " replace(FAmountincludetax,char(9),'') as FAmountincludetax,replace(FTaxRate,char(9),'') as FTaxRate,replace(币种,char(9),'') as 币种,replace(FModel,char(9),'') as FModel," +
                        " replace(replace(replace(FNote,char(9),''),char(10),''),char(13),'') as FNote,replace(FHeadSelfI0453,char(9),'') as FHeadSelfI0453,replace(审核人,char(9),'') as 审核人,replace(FTranType,char(9),'') as FTranType," +
                        " replace(新产品线201304,char(9),'') as 新产品线201304,replace(新产品线201403,char(9),'') as 新产品线201403,replace(金税发票号,char(9),'') as 金税发票号,replace(FAddress,char(9),'') as FAddress," +
                        " replace(FPhone,char(9),'') as FPhone,replace(FAccount,char(9),'') as FAccount,replace(Fnumber2,char(9),'') as Fnumber2,replace(FAmount,char(9),'') as FAmount," +
                        " replace(FTaxAmount,char(9),'') as FTaxAmount,replace(FTaxNum,char(9),'') as FTaxNum,replace(业务员,char(9),'') as 业务员,replace(FName,char(9),'') as FName,replace(FPrice,char(9),'') as FPrice,账套," +
                        " Row_Number() over (order by fnumber,账套)  as FRowindex FROM ( " +
                        " SELECT v_icsale_full.FNumber as FNumber, v_icsale_full.FBillNo as FBillNo, v_icsale_full.FHeadSelfI0450 as FHeadSelfI0450, v_icsale_full.FHeadSelfI0454 as FHeadSelfI0454," +
                        " v_icsale_full.SO as SO,t_ICItem.FAlias as FAlias,convert(varchar(10),v_icsale_full.FDate,126) as FDate, v_icsale_full.FHelpCode as FHelpCode," +
                        " v_icsale_full.FName1 as FName1,v_icsale_full.FName2 as FName2, v_icsale_full.FQty as FQty,v_icsale_full.FtaxPrice as FtaxPrice," +
                        " v_icsale_full.FAmountincludetax as FAmountincludetax,v_icsale_full.FTaxRate as FTaxRate, v_icsale_full.币种 as 币种,v_icsale_full.FModel as FModel," +
                        " v_icsale_full.FNote as FNote, v_icsale_full.FHeadSelfI0453 as FHeadSelfI0453,  v_icsale_full.审核人 as 审核人, v_icsale_full.FTranType as FTranType," +
                        " v_icsale_full.新产品线201304 as 新产品线201304, v_icsale_full.新产品线201403 as 新产品线201403, v_icsale_full.金税发票号 as 金税发票号,v_icsale_full.FAddress as FAddress," +
                        " v_icsale_full.FPhone as FPhone, v_icsale_full.FAccount as FAccount, v_icsale_full.Fnumber2 as Fnumber2,  v_icsale_full.FAmount as FAmount," +
                        " v_icsale_full.FTaxAmount as FTaxAmount,v_icsale_full.FTaxNum as FTaxNum,v_icsale_full.业务员 as 业务员, v_icsale_full.FName as FName,  v_icsale_full.FPrice AS FPrice,'BID' as 账套,FCheckDate " +
                        " FROM {oj AIS20101225174316.dbo.v_icsale_full v_icsale_full LEFT OUTER JOIN AIS20101225174316.dbo.t_ICItem t_ICItem ON v_icsale_full.Fnumber2 = t_ICItem.FNumber} " +
                        " union all " +
                        " SELECT v_icsale_full.FNumber as FNumber, v_icsale_full.FBillNo as FBillNo, v_icsale_full.FHeadSelfI0450 as FHeadSelfI0450, v_icsale_full.FHeadSelfI0454 as FHeadSelfI0454," +
                        " v_icsale_full.SO as SO,t_ICItem.FAlias as FAlias,convert(varchar(10),v_icsale_full.FDate,126) as FDate, v_icsale_full.FHelpCode as FHelpCode," +
                        " v_icsale_full.FName1 as FName1,v_icsale_full.FName2 as FName2, v_icsale_full.FQty as FQty,v_icsale_full.FtaxPrice as FtaxPrice," +
                        " v_icsale_full.FAmountincludetax as FAmountincludetax,v_icsale_full.FTaxRate as FTaxRate, v_icsale_full.币种 as 币种,v_icsale_full.FModel as FModel," +
                        " v_icsale_full.FNote as FNote, v_icsale_full.FHeadSelfI0453 as FHeadSelfI0453,  v_icsale_full.审核人 as 审核人, v_icsale_full.FTranType as FTranType," +
                        " v_icsale_full.新产品线201304 as 新产品线201304, v_icsale_full.新产品线201403 as 新产品线201403, v_icsale_full.金税发票号 as 金税发票号,v_icsale_full.FAddress as FAddress," +
                        " v_icsale_full.FPhone as FPhone, v_icsale_full.FAccount as FAccount, v_icsale_full.Fnumber2 as Fnumber2,  v_icsale_full.FAmount as FAmount," +
                        " v_icsale_full.FTaxAmount as FTaxAmount,v_icsale_full.FTaxNum as FTaxNum,v_icsale_full.业务员 as 业务员, v_icsale_full.FName as FName,  v_icsale_full.FPrice AS  FPrice,'MIDDBJ' as 账套	,FCheckDate " +
                        " FROM {oj AIS20070306163042.dbo.v_icsale_full v_icsale_full LEFT OUTER JOIN AIS20070306163042.dbo.t_ICItem t_ICItem ON v_icsale_full.Fnumber2 = t_ICItem.FNumber} )A " +
                        " WHERE FCheckDate>='" + FDate + "' And FCheckDate<='" + FDate + "'";
                 cmd = db.GetSqlStringCommand(sql);
                 DataTable dt = db.ExecuteDataTable(cmd);
                 //string FDate = DateTime.Now.ToString("yyyy-MM-dd");
                 string sFilePath = "C:\\K3";

                 if (!Directory.Exists(sFilePath))//验证路径是否存在
                 {
                     Directory.CreateDirectory(sFilePath);
                     //不存在则创建
                 }

                 ExportExcelFile(dt, "C:\\K3\\BIBJ Sales report " + FDate + ".txt");

                 return true;
                 //MessageBox.Show("文件生成成功请在C:\\K3\\下查看");
             }
             catch
             {
                 SFTPHelper SFT = new SFTPHelper("47.96.39.95", "22", "ftpdatauser", "7z1#7usFYW%O");
                 SFT.SendMailUse("数据库连接出现问题,请管理员查看", "BIBJ Sales report");

                 return false;
             }
         }

         private void GetTXT()
         {
             int i;
             string Names;
             SFTPHelper SFT = new SFTPHelper("47.96.39.95", "22", "ftpdatauser", "7z1#7usFYW%O"); //"192.168.2.100", "22", "Administrator", "891025"   //"47.97.60.80", "22", "ftpdatauser", "I#3FcJS1Lb%#")
             string FDate = DateTime.Now.AddDays(-1).ToShortDateString();
             //FDate = dateTimePicker1.Text.ToString();

             int a = 0;

             //SFT.GetFileList("K3/", ".txt");
             SFT.Put("C:\\K3\\BIBJ Sales report " + FDate + ".txt", "BIBJ/BIBJ Sales report " + FDate + ".txt");    //opt/ftpdata/BIBJ

             Names = "BIBJ Sales report " + FDate + ".txt";

             ArrayList al = new ArrayList();
             ///opt/ftpdata//BIBJ
             al = SFT.GetFileList("BIBJ/", ".txt");
             //SFT.Get()
             for (i = 0; i < al.Count; i++)
             {
                 if (Names == al[i].ToString())
                 {

                    SFT.SendMailUse("SFTP移动文件成功"+Names, "BIBJ Sales report");
                    a = 1;
                }
             }

             if (a !=1)
             {
                 SFT.SendMailUse("SFTP移动文件失败,请联系K3管理员", "BIBJ Sales report");
             }

         }

         private void button5_Click(object sender, EventArgs e)
         {
             //System.Timers.Timer timer = new System.Timers.Timer();
             timer.Enabled = false;
             timer.Stop();
             button4.Enabled = true;
         }

         private void Form1_Load(object sender, EventArgs e)
         {
             button4_Click (null,EventArgs.Empty );
         }

    }

}
